﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq.Dynamic;
using System.Configuration;
using IronOne.SalesOne.BusinessObjects;
using System.Data.Linq.SqlClient;

namespace IronOne.SalesOne.DataObjects
{
    public class LinqCollectionTransaction
    {
        public int pageSize = Convert.ToInt32(ConfigurationManager.AppSettings["GridCount"]);

        public List<CollectionTransaction> GetCollectionTransaction(string cssNo, string cssStatus, int branchId, string unitId, string SOCode, string sortExpression,int pageNo)
        {
            StringBuilder col = new StringBuilder();
            col.Append(" AND CSSNo != null ");
            if (!String.IsNullOrEmpty(cssNo))
            {
                col.Append(" AND CSSNo = \"" + cssNo + "\"");
            }
            if (!String.IsNullOrEmpty(SOCode))
            {
                col.Append(" AND SOCode = \"" + SOCode + "\"");
            }


            StringBuilder uni = new StringBuilder();
            uni.Append(" AND UnitId != null ");
            if (!String.IsNullOrEmpty(unitId))
                uni.Append(" AND UnitId = " + Convert.ToInt32(unitId) + "");

            


            if (col.Length > 1 || uni.Length > 1)
            {
                if (col.ToString().StartsWith(" AND "))
                    col.Remove(0, 5);
                if (uni.ToString().StartsWith(" AND "))
                    uni.Remove(0, 5);

                using (SODataDataContext db = DataContextFactory.CreateContext())
                {

                    var query = from c in db.CollectionEntities.Where(col.ToString())
                                join user in db.UserEntities on c.SOCode equals user.SOCode
                                join us in db.UsersInStructureEntities on user.UserId equals us.UserId
                                join un in db.UnitEntities.Where(uni.ToString()) on us.NodeId equals un.UnitId
                                where c.SOCode != null && un.BranchId == branchId && us.UserTypeId == 4
                                    && ((from coll in db.CollectionEntities
                                         where (coll.CollectionStatus.ToLower() == "submitted" || coll.CollectionStatus.ToLower() == "unprocessed" || coll.CollectionStatus.ToLower() == "cancelledunprocessed" && coll.CollectionStatus.ToLower() != "cancelled") && coll.CSSNo == c.CSSNo && coll.SOCode == c.SOCode
                                         /*select coll.ReceiptSeqNo).Count() > 0 ? "Syncronized" : ((from colll in db.CollectionEntities
                                                                                                 where (colll.CollectionStatus == "Verified" || colll.CollectionStatus == "Uploaded" || colll.CollectionStatus == "Cancelled" || colll.CollectionStatus == "cancelledverified") && colll.CSSNo == c.CSSNo
                                                                                                 select colll.ReceiptSeqNo).Count() > 0 ? "Completed" : "Nothing")) == cssStatus*/
                                         select coll.ReceiptSeqNo).Count() > 0 ? "Syncronized" : "Completed") == cssStatus

                                group c by new { c.CSSNo, c.SOCode } into g

                                

                                //select new CollectionTransaction()
                                //{
                                //    CSSNo = c.CSSNo,
                                //    SOCode = c.SOCode,
                                //    Status = cssStatus,
                                //    LastSyncDate = c.LastSyncDate
                                //};
                                select new CollectionTransaction()
                                {

                                    CSSNo = g.Key.CSSNo,
                                    SOCode = g.Key.SOCode,
                                    Status = cssStatus,
                                    LastSyncDate = g.Max(c => c.LastSyncDate),
                                    CollectionAmount = g.Sum(c => c.CollectionAmt)
                                };

                   
                    //var pageData = query.Skip((pageNo - 1) * pageSize).Take(pageSize);
                    //return pageData.Distinct().ToList();    
                    var pageData = query.Distinct();
  
                    //return pageData.OrderByDescending(c=>c.CSSNo).Skip((pageNo - 1) * pageSize).Take(pageSize).ToList();
                    
                    //Suren - 2012-05-15
                    //Remove the cancelled amount form the total (bug fix)
                    List<CollectionTransaction> pageDataList = pageData.OrderByDescending(c => c.CSSNo).Skip((pageNo - 1) * pageSize).Take(pageSize).ToList();
                    int temp = 0;
                    foreach (var pd in pageDataList)
                    {
                        var qry0 = from c in db.CollectionEntities
                                   where (c.CSSNo == pd.CSSNo && c.SOCode == pd.SOCode && c.CollectionStatus.ToLower() == "cancelled")
                                   group c by new { c.CSSNo, c.SOCode }
                                       into x
                                       select x.Sum(c => c.CollectionAmt);

                        foreach (var d in qry0)
                        {
                            temp = Convert.ToInt32(d);
                            pd.CollectionAmount = pd.CollectionAmount - temp;
                        }
                    }
                    return pageDataList;
                }
            }
            return null;
        }

        private string getcssStatus(string css,SODataDataContext db)
        {
            int test = (from col in db.CollectionEntities
                       where (col.CollectionStatus == "Synchronised" || col.CollectionStatus == "Unprocessed")  && col.CSSNo == css
                       select col.ReceiptSeqNo).Count();

            if (test > 0)
                return "Submitted";

            return "Completed";
        }

        public List<CollectionTransaction> GetCollectionByBranch(int branchId, DateTime fromDate, DateTime toDate)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                var query = from col in db.CollectionEntities
                            join user in db.UserEntities on col.SOCode equals user.SOCode
                            join us in db.UsersInStructureEntities on user.UserId equals us.UserId
                            join un in db.UnitEntities on us.NodeId equals un.UnitId
                            where (col.VerifiedDate >= fromDate && col.VerifiedDate <= toDate) && col.SOCode != null && 
                                   us.UserTypeId == 4 && un.BranchId == branchId
                            select col;

                return query.Select(c => Mapper.ToCollectionTransactionObject(c)).ToList();
            }
        }

        /// <summary>
        /// Suren Manawatta
        /// 2012-11-05
        /// Same as the above function this fasilitates multiple branches
        /// </summary>
        /// <param name="branchIds"></param>
        /// <param name="fromDate"></param>
        /// <param name="toDate"></param>
        /// <returns></returns>
        public List<CollectionTransaction> GetCollectionByBranchList(int[] branchIds, DateTime fromDate, DateTime toDate)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                var query = from col in db.CollectionEntities
                            join user in db.UserEntities on col.SOCode equals user.SOCode
                            join us in db.UsersInStructureEntities on user.UserId equals us.UserId
                            join un in db.UnitEntities on us.NodeId equals un.UnitId
                            where (col.VerifiedDate >= fromDate && col.VerifiedDate <= toDate) && col.SOCode != null &&
                                     us.UserTypeId == 4 && (branchIds.Contains(un.BranchId))
                            select col;

                return query.Select(c => Mapper.ToCollectionTransactionObject(c)).ToList();
            }
        }

        public List<CollectionTransaction> GetCSSDetailsByCSSNo(string CSSNo, string payMethod, string SOCode,string Status, bool isHQMgr)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<CollectionEntity> query = db.CollectionEntities;
                StringBuilder sb = new StringBuilder();
                if (!String.IsNullOrEmpty(Status) && Status == "Syncronized")
                {
                    if(isHQMgr)
                    //sb.Append(" CollectionStatus = \"Submitted\" OR CollectionStatus = \"Unprocessed\" OR CollectionStatus = \"Verified\" OR CollectionStatus = \"Uploaded\" OR CollectionStatus = \"Cancelled\" ");
                        sb.Append(" CollectionStatus = \"Submitted\" OR CollectionStatus = \"Unprocessed\" OR CollectionStatus = \"Cancelled\" OR CollectionStatus = \"CancelledUnprocessed\" ");
                    else
                        sb.Append(" CollectionStatus = \"Submitted\" OR CollectionStatus = \"Cancelled\" ");
                }
                if (!String.IsNullOrEmpty(SOCode) && Status == "Completed")
                {
                    //sb.Append(" CollectionStatus = \"Verified\" OR CollectionStatus = \"Uploaded\" OR CollectionStatus = \"Cancelled\" ");
                    sb.Append(" CollectionStatus = \"Verified\" OR CollectionStatus = \"Uploaded\" OR CollectionStatus = \"CancelledVerified\" ");
                }

                query = from c in db.CollectionEntities.Where(sb.ToString())
                        where c.CSSNo == CSSNo && c.PaymentMethod == payMethod && c.SOCode == SOCode
                        select c;
                return query.Distinct().Select(c => Mapper.ToCollectionTransactionObject(c)).ToList();
            }
        }

        //public int UpdateCollection(List<CollectionTransaction> collections)
        //{
        //    using (SODataDataContext db = DataContextFactory.CreateContext())
        //    {
        //        try
        //        {
        //            List<CollectionEntity> entities = Mapper.ToCollectionTransactionEntity(collections);
        //            db.CollectionEntities.AttachAll(entities, false);
        //            db.SubmitChanges();
        //            return 1;
        //        }
        //        catch (ChangeConflictException)
        //        {
        //            throw new Exception("Collection update error.");
        //        }
        //        catch (Exception)
        //        {
        //            return 0;
        //        }
        //    }
        //}

        public int UpdateCollectionStatus(string SOCOde, long ReceiptNo, string status,string verifiedBy)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    CollectionEntity collection = db.CollectionEntities.Single(c => c.SOCode == SOCOde && c.ReceiptSeqNo == ReceiptNo);
                    collection.CollectionStatus = status;
                    collection.VerifiedBy = verifiedBy;
                    collection.VerifiedDate = DateTime.Now;
                    collection.DoneBy = verifiedBy;
                    db.SubmitChanges();
                    return 1;
                }
                catch
                {
                    return -1;
                }
            }



        }

        public int CancelCollectionStatus(string SOCOde, long ReceiptNo, string status,string doneBy)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    CollectionEntity collection = db.CollectionEntities.Single(c => c.SOCode == SOCOde && c.ReceiptSeqNo == ReceiptNo);
                    
                    collection.CollectionStatus = status;
                    collection.LastUpdated = DateTime.Now;
                    collection.DoneBy = doneBy;
                    db.SubmitChanges();
                    return 1;
                }
                catch
                {
                    return -1;
                }
            }



        }


        public int UpdateCollectionGrid(string SOCOde, long receiptNo, 
                                        string contr_ClientID,
                                        string loanInt,
                                        string type,
                                        string bankedStatus,
                                        string chqNo,
                                        string bankCD,
                                        string branchCD,
                                        string doneBy)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    CollectionEntity collection = db.CollectionEntities.Single(c => c.SOCode == SOCOde && c.ReceiptSeqNo == receiptNo);
                    if (type.ToUpper() == "DEPOSIT")
                    {

                        collection.CustomerId = contr_ClientID;
                       // collection.ContractNo = contr_ClientID;

                    }
                    else if (type.ToUpper() == "RENEWAL" || type.ToUpper() == "LOAN")
                    {
                       // collection.CustomerId = contr_ClientID;
                        collection.ContractNo = contr_ClientID;
                    }
                    
                    

                    collection.BankState = bankedStatus;
                    collection.LoanInt = Convert.ToDouble(loanInt);
                    collection.ChequeNo = chqNo;
                    collection.BankCode = bankCD;
                    collection.BranchCode = branchCD;
                    collection.DoneBy = doneBy;
                    collection.VerifiedBy = doneBy;

                    if (collection.CollectionStatus.ToUpper() == "CANCELLED" || collection.CollectionStatus.ToUpper() == "CANCELLEDVERIFIED")
                        collection.CollectionStatus = "CancelledVerified";
                    else
                        collection.CollectionStatus = "Verified";

                    collection.VerifiedDate = DateTime.Now;
                    db.SubmitChanges();
                    return 1;
                }
                catch
                {
                    return -1;
                }
            }
        }

        public int UpdateCahierRemarks(string SOCOde, long ReceiptNo, string remarks,string doneBy)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    CollectionEntity collection = db.CollectionEntities.Single(c => c.SOCode == SOCOde && c.ReceiptSeqNo == ReceiptNo);
                    collection.RemarksCashier = remarks;
                    collection.LastUpdated = DateTime.Now;
                    collection.DoneBy = doneBy;
                    db.SubmitChanges();
                    return 1;
                }
                catch
                {
                    return -1;
                }
            }



        }

        public int UpdateChangeLog(string SOCOde, long ReceiptNo, string newLog,string loggedUser)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    CollectionEntity collection = db.CollectionEntities.Single(c => c.SOCode == SOCOde && c.ReceiptSeqNo == ReceiptNo);
                    collection.ChangeLog = collection.ChangeLog + "~<Change; " + collection.ChequeNo + "-" + collection.BankCode + "-" + collection.BranchCode + " , " + DateTime.Now.ToString() + " , " + loggedUser + " >";
                    collection.LastUpdated = DateTime.Now;
                    db.SubmitChanges();
                    return 1;
                }
                catch
                {
                    return -1;
                }
            }



        }


        public List<CollectionTransaction> GetReceiptDetails(long recNo, string SOCode)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                //var query = db.CollectionEntities;
                var query = from col in db.CollectionEntities
                        join idt in db.IDTypeEntities on col.CustomerIDType equals idt.Code_Id
                        join ms in db.MaritalStatusEntities on col.CivilStatus equals ms.Code_Id
                        join sal in db.SalutationEntities on col.CivilStatus equals sal.Code_Id
                        join us in db.UserEntities on col.SOCode equals us.SOCode

                        where col.SOCode == SOCode  && col.ReceiptSeqNo == recNo
                        select new CollectionTransaction
                            {strIDType = idt.Description,
                            IdNo=col.CustomerIDNo,
                            strSalutation =  sal.Description,
                            CustomerName = col.CustomerName,
                            Address1 = col.Address1,
                            Address2 = col.Address2,
                            Address3 = col.Address3,
                            PostCode = col.PostalCode,
                            ClientID = col.CustomerId,
                            BankCode = col.BankCode,
                            BranchCode = col.BranchCode,
                            CheqNo = col.ChequeNo,
                            CheqeDate = (DateTime)col.ChequeDate != null ? (DateTime)col.ChequeDate :DateTime.Now ,
                            PaymentMethod = col.PaymentMethod,
                            CollectionAmount = col.CollectionAmt,
                            //Suren
                            ReceiptDate = (DateTime)col.ReceiptDate,
                            LastSyncDate = col.LastSyncDate, 
                            //Signature = (col.Signature == null) ? null : Mapper.ByteToStream((byte[])(col.Signature).ToArray()),
                            AgentName =  us.Name,
                            AgentSalutation = (us.Gender == "Female") ? "Mrs" : "Mr"
                            };

                return query.ToList();
            }
        }

        public List<CollectionTransaction> GetRemarks(string SOCOde, long ReceiptNo)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                var query = from c in db.CollectionEntities
                            where c.SOCode == SOCOde && c.ReceiptSeqNo == ReceiptNo
                            let status = c.CollectionStatus.ToLower()
                            select new CollectionTransaction
                            {
                                RemarksCashier = c.RemarksCashier,
                                RemarksSO = (status == "cancelled" || status == "cancelledverified" ? c.Reason : c.Remarks),
                                ChangeLog = c.ChangeLog,
                                Reason = c.Reason
                            };
                return query.ToList();
            }
        }

    }
   
}
